﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.SQLite;
using System.Configuration;
using System.IO;

namespace AppEntity
{
    public partial class Common
    {
        public partial class DataBase
        {
            public partial class Sqlite : System.IDisposable
            {
                public SQLiteConnection conn;

                public class SqlteParam
                {
                    public string Name;
                    public DbType Type;
                    public int Size;
                    public object Value;
                }

                public static void CreateSqlFile(string FileFullPath)
                {
                    SQLiteConnection.CreateFile(FileFullPath);
                }

                /// <summary>
                /// 创建数据库链接，并打开数据库。
                /// </summary>
                public Sqlite(string DataBaseFileFullPath)
                {
                    if (!File.Exists(DataBaseFileFullPath))
                    {
                        SQLiteConnection.CreateFile(DataBaseFileFullPath);
                    }
                    string ConnectionString = @"data source=" + DataBaseFileFullPath;
                    this.conn = new SQLiteConnection(ConnectionString);
                    this.conn.Open();
                }

                public void Close()
                {
                    this.conn.Close();
                }



                /// <summary>
                /// 仅支持select语句，并使用DataReader向前读取数据。
                /// </summary>
                /// <param name="sql">Sql 语句</param>
                /// <returns>返回Xml字符串:/root/item</returns>
                public string SelectData2Xml(string sql)
                {
                    return SelectData2Xml(sql, null);
                }
                /// <summary>
                /// 仅支持select语句，并使用DataReader向前读取数据。
                /// </summary>
                /// <param name="sql">Sql 语句</param>
                /// <param name="Params">参数列表</param>
                /// <returns>返回Xml字符串:/root/item</returns>
                public string SelectData2Xml(string sql, DataBase.Sqlite.SqlteParam[] Params)
                {
                    StringBuilder sb = new StringBuilder();
                    XmlWriterSettings setting = new XmlWriterSettings();
                    setting.Encoding = Encoding.UTF8;
                    //setting.OmitXmlDeclaration = true;
                    setting.Indent = true;
                    XmlWriter writer = XmlWriter.Create(sb, setting);
                    writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
                    writer.WriteStartElement("root");

                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = this.conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql.Trim();
                    if (Params != null && Params.Length > 0)
                    {
                        cmd.Parameters.AddRange(GetParams(Params));

                    }
                    SQLiteDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            writer.WriteStartElement("item");
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                writer.WriteAttributeString(reader.GetName(i), reader[reader.GetName(i)].ToString());
                            }
                            writer.WriteEndElement();
                        }
                    }
                    reader.Close();
                    cmd.Dispose();

                    writer.WriteEndElement();
                    writer.Close();

                    return sb.ToString();
                }


                public object ExecuteScalar(string sql)
                {
                    return ExecuteScalar(sql, null);
                }

                public object ExecuteScalar(string sql, DataBase.Sqlite.SqlteParam[] Params)
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = this.conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    if (Params != null && Params.Length > 0)
                    {
                        cmd.Parameters.AddRange(GetParams(Params));
                    }
                    object first = cmd.ExecuteScalar();
                    cmd.Dispose();
                    return first;
                }

                /// <summary>
                /// 执行Sql语句，返回受影响的行数
                /// </summary>
                /// <param name="sql"></param>
                /// <returns>返回受影响的行数</returns>
                public int ExecuteSql(string sql)
                {
                    return ExecuteSql(sql, null);
                }
                /// <summary>
                /// 参数化查询
                /// </summary>
                /// <param name="sql"></param>
                /// <param name="Params"></param>
                /// <returns></returns>
                public int ExecuteSql(string sql, DataBase.Sqlite.SqlteParam[] Params)
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = this.conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    if (Params != null && Params.Length > 0)
                    {
                        cmd.Parameters.AddRange(GetParams(Params));
                    }
                    int lines = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    return lines;
                }


                private static SQLiteParameter[] GetParams(DataBase.Sqlite.SqlteParam[] Params)
                {
                    SQLiteParameter[] Arr = new SQLiteParameter[Params.Length];
                    for (int i = 0; i < Params.Length; i++)
                    {
                        SQLiteParameter param = new SQLiteParameter();
                        if (!string.IsNullOrEmpty(Params[i].Name))
                        {
                            string n = Params[i].Name;
                            if (!n.StartsWith("@") && !n.StartsWith("$"))
                            {
                                n = "@" + n;
                            }
                            param.ParameterName = n;
                        }
                        try
                        {
                            //string t = Params[i].Type.ToString();
                            param.DbType = Params[i].Type;
                        }
                        catch (Exception ex)
                        {
                        }
                        if (Params[i].Size != null)
                        {
                            param.Size = Params[i].Size;
                        }
                        param.Value = Params[i].Value;
                        Arr[i] = param;
                    }
                    return Arr;
                }

                /// <summary>
                /// 执行指定的存储过程，传入参数。返回受影响的行数
                /// </summary>
                /// <param name="StoredProcedure"></param>
                /// <param name="param"></param>
                /// <returns>返回受影响的行数</returns>
                //public int ExecuteSql(string StoredProcedure, Dictionary<string, object> param)
                //{
                //    SQLiteCommand cmd = new SQLiteCommand();
                //    cmd.Connection = this.conn;
                //    cmd.CommandType = CommandType.StoredProcedure;
                //    cmd.CommandText = StoredProcedure;
                //    foreach (string key in param.Keys)
                //    {
                //        SQLiteParameter p = cmd.CreateParameter();
                //        p.ParameterName = key;
                //        p.Value = param[key];

                //        cmd.Parameters.Add(p);                    
                //        //cmd.Parameters.Add(
                //    }
                //    int lines = cmd.ExecuteNonQuery();
                //    cmd.Dispose();
                //    return lines;
                //}

                /// <summary>
                /// 执行查询语句，返回 DataTable
                /// </summary>
                /// <param name="sql"></param>
                /// <returns></returns>
                public DataTable GetDataTable(string sql)
                {                    
                    SQLiteCommand cmd = new SQLiteCommand(sql, this.conn);
                    
                    SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    Adapter.Fill(dt);
                    cmd.Dispose();
                    Adapter.Dispose();
                    return dt;
                }

                public DataSet GetDataSet(string sql)
                {
                    SQLiteCommand cmd = new SQLiteCommand(sql, this.conn);
                    SQLiteDataAdapter Adapter = new SQLiteDataAdapter(cmd);
                    DataSet Set = new DataSet();
                    Adapter.Fill(Set);
                    cmd.Dispose();
                    Adapter.Dispose();
                    return Set;
                }

                public SQLiteDataReader GetDataReader(string sql)
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, this.conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        SQLiteDataReader reader = cmd.ExecuteReader();
                        return reader;
                    }
                }

                /// <summary>
                /// 供程序员显式调用的Dispose方法
                /// </summary>
                public void Dispose()
                {

                    //调用带参数的Dispose方法，释放托管和非托管资源
                    Dispose(true);
                    //手动调用了Dispose释放资源，那么析构函数就是不必要的了，这里阻止GC调用析构函数
                    System.GC.SuppressFinalize(this);
                }

                /// <summary>
                /// protected的Dispose方法，保证不会被外部调用。传入bool值disposing以确定是否释放托管资源
                /// </summary>
                /// <param name="disposing"></param>
                protected void Dispose(bool disposing)
                {
                    if (disposing)
                    {
                        this.conn.Close();
                        this.conn.Dispose();
                        ///TODO:在这里加入清理"托管资源"的代码，应该是xxx.Dispose();
                    }
                    ///TODO:在这里加入清理"非托管资源"的代码
                }

                ~Sqlite()
                {
                    Dispose(false);//释放非托管资源
                }
            }
        }
    }
}
